//
//  DataBaseHandle.m
//  行记
//
//  Created by dllo on 15/9/29.
//  Copyright (c) 2015年 lhch. All rights reserved.
//

#import "DataBaseHandle.h"
#import <sqlite3.h>
#import "YZLCollectionModel.h"

@implementation DataBaseHandle

+ (instancetype)shareDataBaseHandle {
    static DataBaseHandle *databaseHandle = nil;
    if (databaseHandle == nil) {
        databaseHandle = [[DataBaseHandle alloc] init];
    }
    return databaseHandle;
}

static sqlite3 *sql;

#pragma mark - 开启数据库
- (BOOL)openSql {

    /* 创建数据库文件路径 */
    NSString *sqlPath = [[NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject] stringByAppendingPathComponent:@"CarCollection.sqlite"];
    NSLog(@"%@", sqlPath);

    /* 判断数据库是否打开 */

//    /* 数据库文件和数据库对象关联(即打开数据库) */
    int result = sqlite3_open(sqlPath.UTF8String, &sql);
    if (SQLITE_OK == result) {
        return YES;
    } else {
        return NO;
    }
}

#pragma mark - 创建数据库列表
- (BOOL)createTable:(NSString *)table {
    /* 创建 SQL 语句 */
    NSString *creatrSql = [NSString stringWithFormat:@"CREATE TABLE IF NOT EXISTS %@ (number INTEGER PRIMARY KEY AUTOINCREMENT, id TEXT, title TEXT, image TEXT)", table];
    //INTEGER
    /* 对数据库进行 SQL 语句操作 */
    int result = sqlite3_exec(sql, creatrSql.UTF8String, nil, nil, nil);
    if (SQLITE_OK == result) {
        return YES;
    } else {
        return NO;
    }
}

#pragma mark - 插入数据库
- (BOOL)insertWithCollect:(YZLCollectionModel *)model withTable:(NSString *)table {
    NSString *insertSql = [NSString stringWithFormat:@"INSERT INTO %@ (id, title, image) VALUES('%@', '%@', '%@')", table, model.nID, model.title, model.image];
    int result = sqlite3_exec(sql, insertSql.UTF8String, nil, nil, nil);
    if (SQLITE_OK == result) {
        return YES;
    } else {
        return NO;
    }
}

#pragma mark - 更新（未写完）
- (BOOL)updataWithCollect:(YZLCollectionModel *)model withTable:(NSString *)table {
    NSString *updataSql = [NSString stringWithFormat:@"UPDATE %@ SET id = '%@', title = '%@' image = '%@'", table, model.nID, model.title, model.image];
    int result = sqlite3_exec(sql, updataSql.UTF8String, NULL, NULL, NULL);
    if (SQLITE_OK == result) {
        return YES;
    } else {
        return NO;
    }
}

#pragma mark - 删除
- (BOOL)deleteWithNid:(NSString *)nid withTable:(NSString *)table{
    NSString *deleteSQL = [NSString stringWithFormat:@"DELETE FROM %@ WHERE id = '%@'", table, nid];
    
    int result = sqlite3_exec(sql, deleteSQL.UTF8String, NULL, NULL, NULL);
    
    if (SQLITE_OK == result) {
        return YES;
    } else {
        return NO;
    }
    
}

#pragma mark - 查询
- (NSArray *)selectStudentWithTable:(NSString *)table {
    
    /* 1.创建一个数组，用于存放符合条件的数据 */
    NSMutableArray *mArr = [NSMutableArray array];
    
    /* 2.SQL语句 */
    NSString *selectSQL = [NSString stringWithFormat:@"SELECT * FROM %@", table];
    
    /* 核心 API：sqlite3_stmt */
    /* 3.1 创建 准备好的语句对象(sqlite3_stmt) */
    sqlite3_stmt *stmt = nil;
    
    /* 3.2 编译 SQL 语句 */
    int result = sqlite3_prepare_v2(sql, selectSQL.UTF8String, - 1, &stmt, nil);
    
    if (SQLITE_OK == result) {

        /*3.3 逐行进行查询
         * 当sqlite3_step（）返回值为 SQLUTE_row时，说明还有数据可查询
         */
        /**
         *  @param 1: stmt 对象
         *  @param 2: 要提取值所在的列 （从0开始）
         */
        
        while (SQLITE_ROW == sqlite3_step(stmt)) {
            /* 如果要对查询结果结束进行操作，调用 sqlites_函数参数簇 */
            const unsigned char *id = sqlite3_column_text(stmt, 1);
            const unsigned char *title = sqlite3_column_text(stmt, 2);
            const unsigned char *image = sqlite3_column_text(stmt, 3);
            /* 3.4  创建 stu对象，进行复制，之后放入数组中 */
            YZLCollectionModel *model = [[YZLCollectionModel alloc] init];
            model.nID = [NSString stringWithUTF8String:(const char *)id];
            model.title = [NSString stringWithUTF8String:(const char *)title];
            model.image = [NSString stringWithUTF8String:(const char *)image];
            [mArr addObject:model];

        
        }
        sqlite3_finalize(stmt);
        
    }
    return mArr;
}

#pragma mark - 删除数据库
- (BOOL)dropTable:(NSString *)table {
    NSString *dropSQL = [NSString stringWithFormat:@"DROP TABLE IF EXISTS %@", table];
    
    int result = sqlite3_exec(sql, dropSQL.UTF8String, NULL, NULL, NULL);
    
    if (SQLITE_OK == result) {
        return YES;
    } else {
        return NO;
    }
}

#pragma mark - 关闭数据库
- (BOOL)closeDB {
    int result = sqlite3_close(sql);
    
    sql = nil;
    if (SQLITE_OK == result) {
        return YES;
    } else {
        return NO;
    }
}



@end
